package com.fduss.artStore.dao.impl;

import com.fduss.artStore.dao.CustomerDao;
import com.fduss.artStore.entity.Customer;
import com.fduss.artStore.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author duocai wu
 * @Date 2017/6/24
 * @Time 13:31
 */
public class CustomerDaoImpl implements CustomerDao {
    private JdbcUtil util;

    public CustomerDaoImpl() {
        this.util = new JdbcUtil();
    }

    @Override
    public Customer add(Customer customer) {
        Connection conn = util.getConnection();
        //language=MySQL
        String sql = "INSERT INTO customers(CustomerID, FirstName, LastName, Address, City, Region, Country, Postal, Phone, Email, Privacy)" +
                " VALUES (?,?,?,?,?,?,?,?,?,?,?)"; // 11
        PreparedStatement pst = null;
        boolean flag = false;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, customer.getCustomerId());
            pst.setString(2, customer.getFirstName());
            pst.setString(3, customer.getLastName());
            pst.setString(4, customer.getAddress());
            pst.setString(5, customer.getCity());
            pst.setString(6, customer.getRegion());
            pst.setString(7, customer.getCountry());
            pst.setString(8, customer.getPostal());
            pst.setString(9, customer.getPhone());
            pst.setString(10, customer.getEmail());
            pst.setString(11, customer.getPrivacy());
            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(null, pst, conn);
        }
        return flag ? customer : null;
    }

    @Override
    public void delete(int customerId) {
        Connection conn = util.getConnection();
        String sql = "DELETE FROM customers WHERE CustomerID = ?";
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, customerId);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(null, pst, conn);
        }
    }

    @Override
    public Customer update(Customer customer) {

        Connection conn = util.getConnection();
        // FirstName, LastName, Address, City, Region, Country, Postal, Phone, Email, Privacy)
        //language=MySQL
        String sql = "UPDATE  customers SET FirstName = ?, LastName = ?, Address = ?, City = ?," +
                "Region = ?, Country = ?, Postal = ?, Phone = ?, Email = ?, Privacy = ? " +
                "WHERE CustomerID = ?";
        PreparedStatement pst = null;
        boolean flag = false;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, customer.getFirstName());
            pst.setString(2, customer.getLastName());
            pst.setString(3, customer.getAddress());
            pst.setString(4, customer.getCity());
            pst.setString(5, customer.getRegion());
            pst.setString(6, customer.getCountry());
            pst.setString(7, customer.getPostal());
            pst.setString(8, customer.getPhone());
            pst.setString(9, customer.getEmail());
            pst.setString(10, customer.getPrivacy());
            pst.setInt(11, customer.getCustomerId());
            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(null, pst, conn);
        }

        return flag ? customer : null;
    }

    @Override
    public List<Customer> getAll() {
        Connection conn = util.getConnection();
        //language=MySQL
        String sql = "SELECT * FROM customers";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Customer> customers = new ArrayList<>();
        try {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                customers.add(buildCustomer(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(rs, pst, conn);
        }

        return customers;
    }

    @Override
    public Customer getOne(int customerId) {
        Connection conn = util.getConnection();
        //language=MySQL
        String sql = "SELECT * FROM customers WHERE CustomerID = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Customer customer = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, customerId);
            rs = pst.executeQuery();
            if (rs.next()) {
                customer = buildCustomer(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close(rs, pst, conn);
        }

        return customer;
    }

    private Customer buildCustomer(ResultSet rs) throws SQLException {
        // FirstName, LastName, Address, City, Region, Country, Postal, Phone, Email, Privacy)
        return new Customer(rs.getInt("CustomerID"), rs.getString("FirstName"),
                rs.getString("LastName"), rs.getString("Address"),
                rs.getString("City"), rs.getString("Region"),
                rs.getString("Country"), rs.getString("Postal"),
                rs.getString("Phone"), rs.getString("Email"),
                rs.getString("Privacy"));
    }
}
